119-2010: Blistering ETL Performance Using the Intelligent, Dynamic, and Parallel Capabilities of SAS®

ETL from billion-record+ databases is a non-trivial task. By using the unique capabilities of SAS® to analyze the database in advance and then dynamically generating parallel SAS® jobs based on this interrogation, SAS® can effectively performance-tune itself each run for maximum benefit. Get in, get out, get the results. The net effect is to get the fastest possible results with the minimum window of ETL load on the source system. Scalable, efficient, and easy to develop. An added benefit is the data visualization possible post-analysis and pre-ETL to aid data quality checks. Improving your ETL time (in the case study, by 92%), using existing resources, postponing expensive hardware upgrades, dynamically adjusting for ever increasing data volumes and, improving information productivity are powerful arguments for considering the adoption of this approach, where feasible, in your own environment. INTRODUCTION Initially the purpose of this paper was to communicate the huge performance benefits of running your SAS® ETL system with an intelligent, dynamic, parallel approach in a specific case study at a telco client. The approach has since been used with a variety of source databases with similar benefits, so I will explain from a conceptual point of view the principles and then finish with a brief overview of the initial case study. We start with some basic parallel principles and then follow the logic through into the Intelligent, Dynamic and Parallel phases of this paper. Then the case study will be used as an example of applying these principles. Going at length into the exact detail of implementation as per the case study became an increasingly lengthy paper and the main thing to gain from reading this is an understanding of how to apply the principles for your own specific environment, so the intention is to keep it at a high-level conceptual explanation whereby you can possibly see a scenario in your own environment where it can be applies successfully, too. Data Integration SAS Global Forum 2010